﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApp.BLL;
using WebApp.BLL.Evaluate;
using WebApp.BLL.Manage;
using WebApp.Common;
using WebApp.Common.Caching;
using WebApp.Models;
using WebApp.ViewModels;
using WebApp.ViewModels.Report;
using WebApp.Web.Core.Auth;


namespace kzjd.rycp.WebApp.Areas.Report.Controllers
{
    public class OfficeMemberResultController : Controller
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger("WebApp.Log");

        #region 头部部分

        [ChildActionOnly]
        public ActionResult EvalHeader(string id)
        {
            //var model = new T_evaluateService().GetCurrentEval();
            var model = new T_evaluateService().Find(Utils.NvInt(id));

            //计划参与测评人数
            ViewBag.PlanMemberCount = new T_evaluate_deptService().GetEvalMemberCount(model.Id);

            //实际参与测评人数（已提交测评结果的人数）
            ViewBag.MemberCount = new T_evaluate_officememberresultService().GetEvalMemberCount(model.Id);

            return View(model);
        }

        #endregion

        #region 测评完成情况

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public ActionResult Index(string id)
        {
            T_evaluate model = null;

            if (!string.IsNullOrEmpty(id))
            {
                Session["EvaluateYear"] = id;
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            else if (Session["EvaluateYear"] != null)
            {
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            if (model == null)
            {
                model = new T_evaluateService().GetCurrentEval();
            }

            return View(model);
        }

        #endregion

        #region 测评结果汇总表

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public ActionResult Summary(string id)
        {
            T_evaluate model = null;

            if (!string.IsNullOrEmpty(id))
            {
                Session["EvaluateYear"] = id;
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            else if (Session["EvaluateYear"] != null)
            {
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            if (model == null)
            {
                model = new T_evaluateService().GetCurrentEval();
            }

            //测评结果文字
            var dictservice = new S_dictService();
            ViewBag.ResultName_1 = dictservice.GetName("OfficeMemberResult", "10");
            ViewBag.ResultName_2 = dictservice.GetName("OfficeMemberResult", "20");
            ViewBag.ResultName_3 = dictservice.GetName("OfficeMemberResult", "30");

            //测评科室颜色设置
            var deptList = new S_departmentService().ListByDeptType(enmDeptType.Worker.ToString());
            var colorMap = new Dictionary<string, string>();
            foreach (var item in deptList)
            {
                colorMap.Add(item.Id.ToString(), item.Bgcolor);
            }
            ViewBag.ColorMap = Newtonsoft.Json.JsonConvert.SerializeObject(colorMap);

            return View(model);
        }

        #endregion

        #region 好评率排名

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public ActionResult RankingAsc(string id)
        {
            T_evaluate model = null;

            if (!string.IsNullOrEmpty(id))
            {
                Session["EvaluateYear"] = id;
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            else if (Session["EvaluateYear"] != null)
            {
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            if (model == null)
            {
                model = new T_evaluateService().GetCurrentEval();
            }

            ViewBag.Eval_id = model.Id;

            var reportdata = new T_evaluate_officememberresultService().GetRankingResultReport(model.Id);

            return View(reportdata);
        }

        #endregion

        #region 差评率排名

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public ActionResult RankingDesc(string id)
        {
            T_evaluate model = null;

            if (!string.IsNullOrEmpty(id))
            {
                Session["EvaluateYear"] = id;
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            else if (Session["EvaluateYear"] != null)
            {
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            if (model == null)
            {
                model = new T_evaluateService().GetCurrentEval();
            }

            ViewBag.Eval_id = model.Id;

            var reportdata = new T_evaluate_officememberresultService().GetRankingResultReport(model.Id);

            return View(reportdata);
        }

        #endregion

        #region 不了解率

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public ActionResult RankingNoresult(string id)
        {
            T_evaluate model = null;

            if (!string.IsNullOrEmpty(id))
            {
                Session["EvaluateYear"] = id;
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            else if (Session["EvaluateYear"] != null)
            {
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            if (model == null)
            {
                model = new T_evaluateService().GetCurrentEval();
            }

            ViewBag.Eval_id = model.Id;

            //差评数统计
            var baddata = new T_evaluate_officememberresultService().GetBadResultReport(model.Id);

            //无差评项
            ViewBag.NoBadcountModel = baddata
                .Select(p => new OfficeMemberNoBadResultReportModel
                {
                    Dept_id = p.Dept_id,
                    Deptname = p.Deptname,
                    Member_id = p.Member_id,
                    Membername = p.Membername,
                    NoBadcount = ((p.Result_1 == 0 ? 1 : 0) + (p.Result_2 == 0 ? 1 : 0) + (p.Result_3 == 0 ? 1 : 0) + (p.Result_4 == 0 ? 1 : 0) + (p.Result_5 == 0 ? 1 : 0))
                })
                .Where(p => p.NoBadcount > 0)
                .OrderByDescending(p => p.NoBadcount)
                .ToList();

            //不了解率
            var reportdata = new T_evaluate_officememberresultService().GetRankingResultReport(model.Id);

            return View(reportdata);
        }

        #endregion

        #region 测评结果分析

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public ActionResult RankingAnalysis(string id)
        {
            T_evaluate model = null;

            if (!string.IsNullOrEmpty(id))
            {
                Session["EvaluateYear"] = id;
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            else if (Session["EvaluateYear"] != null)
            {
                model = new T_evaluateService().SelectByYear(Utils.NvInt(Session["EvaluateYear"]));
            }
            if (model == null)
            {
                model = new T_evaluateService().GetCurrentEval();
            }

            ViewBag.Eval_id = model.Id;

            var reportdata = new T_evaluate_officememberresultService().GetRankingResultReport(model.Id);

            return View(reportdata);
        }

        #endregion

        #region 查询列表数据（测评完成情况）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult ReportResultJson(int evalId, string sort, string order)
        {
            var service = new T_evaluate_deptService();

            Dictionary<string, object> result = new Dictionary<string, object>();

            //测评科室设置
            var list = service.ListByEvalId(evalId);
            //各科室完成情况
            var resultList = new T_evaluate_officememberresultService().GetDeptEvalMemberCount(evalId);

            var rows = new List<Dictionary<string, object>>();
            foreach (var item in list)
            {
                var rowdata = new Dictionary<string, object>();

                rowdata.Add("id", item.Eval_dept_id);
                rowdata.Add("Eval_deptname", item.Eval_deptname);
                rowdata.Add("Requireusers", item.Requireusers);

                rowdata.Add("iconCls", "icon-empty");
                rowdata.Add("state", "closed");
                rowdata.Add("level", "1");

                int memberCount = 0;
                DateTime? lastUpdateTime;
                rowdata.Add("children", this.GetReportResultChildren(evalId, item, out memberCount, out lastUpdateTime));

                rowdata.Add("Finishedusers", memberCount);
                rowdata.Add("Updatetime", CommonStr.GetDateTimeStr(lastUpdateTime));
                if (item.Requireusers > 0)
                {
                    rowdata.Add("Rate", ((float)memberCount / item.Requireusers).ToString("0.00%"));
                }
                else
                {
                    rowdata.Add("Rate", "0%");
                }

                //var o = resultList.FirstOrDefault(p => p.Eval_dept_id == item.Eval_dept_id);
                //if (o != null)
                //{
                //    rowdata.Add("Finishedusers", o.MemberCount);
                //    rowdata.Add("Updatetime", CommonStr.GetDateTimeStr(o.Updatetime));
                //    if (item.Requireusers > 0)
                //    {
                //        rowdata.Add("Rate", ((float)o.MemberCount / item.Requireusers).ToString("0.00%"));
                //    }
                //    else
                //    {
                //        rowdata.Add("Rate", "0%");
                //    }
                //}
                //else
                //{
                //    rowdata.Add("Finishedusers", "0");
                //    rowdata.Add("Rate", "0%");
                //    rowdata.Add("Updatetime", "");
                //}

                rows.Add(rowdata);
            }

            return Json(rows);

            //result.Add("total", rows.Count);
            //result.Add("rows", rows);

            //return Json(result);
        }

        private List<Dictionary<string, object>> GetReportResultChildren(int evalId, T_evaluate_dept evalDept, out int minCount, out DateTime? lastUpdateTime)
        {
            var deptList = new S_departmentService().ListByDeptType(enmDeptType.Manager.ToString());
            var resultList = new T_evaluate_officememberresultService().GetDeptEvalMemberCountChildren(evalId, evalDept.Eval_dept_id);

            minCount = 0;
            lastUpdateTime = null;
            if (resultList.Any())
            {
                minCount = resultList.Min(p => p.MemberCount);
                lastUpdateTime = resultList.Max(p => p.Updatetime);
            }

            var rows = new List<Dictionary<string, object>>();
            foreach (var item in deptList)
            {
                var rowdata = new Dictionary<string, object>();
                rowdata.Add("id", evalDept.Eval_dept_id + "-" + item.Id);
                rowdata.Add("iconCls", "icon-empty");
                rowdata.Add("level", "2");
                rowdata.Add("Eval_deptname", item.Deptname);
                rowdata.Add("Requireusers", evalDept.Requireusers);

                var o = resultList.FirstOrDefault(p => p.Dest_dept_id == item.Id);
                if (o != null)
                {
                    rowdata.Add("Finishedusers", o.MemberCount);
                    rowdata.Add("Updatetime", CommonStr.GetDateTimeStr(o.Updatetime));
                    if (evalDept.Requireusers > 0)
                    {
                        rowdata.Add("Rate", ((float)o.MemberCount / evalDept.Requireusers).ToString("0.00%"));
                    }
                    else
                    {
                        rowdata.Add("Rate", "0%");
                    }
                }
                else
                {
                    rowdata.Add("Finishedusers", "0");
                    rowdata.Add("Rate", "0%");
                    rowdata.Add("Updatetime", "");
                }

                rows.Add(rowdata);
            }

            return rows;
        }

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult CreateExportFile(int evalId, string sort, string order)
        {
            try
            {
                var service = new T_evaluate_deptService();

                //测评科室设置
                var list = service.ListByEvalId(evalId);
                //各科室完成情况
                var resultList = new T_evaluate_officememberresultService().GetDeptEvalMemberCount(evalId);

                //生成Excel
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                var st_header = ExcelHelper.getHeaderStyle(workbook);
                st_header.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border = ExcelHelper.getContentStyle(workbook);
                st_border.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border_pecent = ExcelHelper.getContentStyle(workbook);
                st_border_pecent.Custom = "0.00%";
                st_border_pecent.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                int icol = 0;
                int irow = 0;

                #region 标题

                //标题
                worksheet.Cells[irow, icol++].PutValue("科室名称");
                worksheet.Cells[irow, icol++].PutValue("要求测评人数");
                worksheet.Cells[irow, icol++].PutValue("已完成人数");
                worksheet.Cells[irow, icol++].PutValue("完成率");
                worksheet.Cells[irow, icol++].PutValue("最后提交时间");

                //样式
                for (int i = 0; i < icol; i++)
                {
                    worksheet.Cells[irow, i].SetStyle(st_header);
                }

                worksheet.Cells.SetRowHeightPixel(irow, 25);

                #endregion

                #region 列宽

                int[] widths = new int[] { 150, 100, 100, 100, 150 };
                for (int i = 0; i < widths.Length; i++)
                {
                    worksheet.Cells.SetColumnWidthPixel(i, widths[i]);
                }

                #endregion

                #region 内容
                irow = 1;
                foreach (var item in list)
                {
                    icol = 0;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(item.Eval_deptname);
                    worksheet.Cells[irow, icol++].PutValue(item.Requireusers);

                    var o = resultList.FirstOrDefault(p => p.Eval_dept_id == item.Eval_dept_id);
                    if (o != null)
                    {
                        worksheet.Cells[irow, icol++].PutValue(o.MemberCount);
                        if (item.Requireusers > 0)
                        {
                            worksheet.Cells[irow, icol++].PutValue((float)o.MemberCount / item.Requireusers);
                        }
                        else
                        {
                            worksheet.Cells[irow, icol++].PutValue(0);
                        }
                        worksheet.Cells[irow, icol++].PutValue(CommonStr.GetDateTimeStr(o.Updatetime));
                    }
                    else
                    {
                        worksheet.Cells[irow, icol++].PutValue(0);
                        worksheet.Cells[irow, icol++].PutValue(0);
                        worksheet.Cells[irow, icol++].PutValue("");
                    }

                    //样式
                    for (int i = 0; i < icol; i++)
                    {
                        if (i == 3)
                        {
                            worksheet.Cells[irow, i].SetStyle(st_border_pecent);
                        }
                        else
                        {
                            worksheet.Cells[irow, i].SetStyle(st_border);

                        }
                    }

                    irow++;
                }
                #endregion

                //临时文件，直接存储在本地
                string path = new S_environmentService().GetValue("upload.temppath");
                //缓存Key值
                string cachekey = Guid.NewGuid().ToString().Replace("-", "");
                //缓存对象(key=临时文件名，value=下载文件名）
                var tempfile = new KeyValue
                {
                    key = cachekey + ".xls",
                    value = string.Format("{0}机关管理人员测评完成情况.xls", DateTime.Now.ToString("yyyy"))
                };
                Utils.CreateDirectory(path);
                workbook.Save(path + tempfile.key, Aspose.Cells.SaveFormat.Excel97To2003);

                //存储文件URL
                new MemoryCacheManager().Remove(cachekey);
                new MemoryCacheManager().Set(cachekey, tempfile, 5);

                return Json(new AjaxResult(AjaxResult.RESULT_OK, "", cachekey));
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return Json(new AjaxResult(AjaxResult.RESULT_ERROR, ex.Message));
            }
        }


        #endregion

        #region 查询列表数据（测评结果汇总表）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult SummaryReportResultJson(int evalId, string sort, string order)
        {
            var service = new T_evaluate_officememberresultService();
            Dictionary<string, object> result = new Dictionary<string, object>();

            ////科室列表
            //var deptList = new S_departmentService().ListByDeptType(enmDeptType.Manager.ToString());
            ////科室人员列表
            //var memberList = new S_dept_memberService().ListByDeptids(deptList.Select(p => p.Id).ToList());

            //已提交票数统计
            var list = service.GetSummaryReport(evalId);
            ////个车间票数统计
            //var deptResultList = service.GetSummaryReportChildren(evalId);

            //foreach (var dept in deptList)
            //{
            //    foreach(var member in memberList.Where(p=>p.Dept_id == dept.Id))
            //    {
            //        var memberResult = list.FirstOrDefault(p => p.Dept_id == dept.Id && p.Member_id == member.Id);
            //    }
            //}

            return Json(list);
            //result.Add("total", list.Count);
            //result.Add("rows", list);

            //return Json(result);
        }

        #endregion

        #region 生成导出文件（测评结果汇总表）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult CreateSummaryExportFile(int evalId, string sort, string order)
        {
            try
            {
                var service = new T_evaluate_officememberresultService();
                var dictservice = new S_dictService();

                var list = service.GetSummaryReport(evalId);

                var resultName_1 = dictservice.GetName("OfficeMemberResult", "10");
                var resultName_2 = dictservice.GetName("OfficeMemberResult", "20");
                var resultName_3 = dictservice.GetName("OfficeMemberResult", "30");

                //生成Excel
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                int icol = 0;
                int irow = 0;

                var st_header = ExcelHelper.getHeaderStyle(workbook);
                st_header.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border = ExcelHelper.getContentStyle(workbook);
                st_border.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                #region 标题

                //标题
                worksheet.Cells[irow, 0].PutValue("科室名称");
                worksheet.Cells[irow, 1].PutValue("人员姓名");
                worksheet.Cells[irow, 2].PutValue("不了解");
                worksheet.Cells[irow, 3].PutValue("服务态度");
                worksheet.Cells[irow, 6].PutValue("深入现场");
                worksheet.Cells[irow, 9].PutValue("工作效率");
                worksheet.Cells[irow, 12].PutValue("廉洁自律");
                worksheet.Cells[irow, 15].PutValue("综合评价");

                //行列合并
                worksheet.Cells.Merge(irow, 0, 2, 1);
                worksheet.Cells.Merge(irow, 1, 2, 1);
                worksheet.Cells.Merge(irow, 2, 2, 1);
                worksheet.Cells.Merge(irow, 3, 1, 3);
                worksheet.Cells.Merge(irow, 6, 1, 3);
                worksheet.Cells.Merge(irow, 9, 1, 3);
                worksheet.Cells.Merge(irow, 12, 1, 3);
                worksheet.Cells.Merge(irow, 15, 1, 3);

                icol = 3;
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_1);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_2);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_3);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_1);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_2);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_3);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_1);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_2);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_3);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_1);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_2);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_3);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_1);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_2);
                worksheet.Cells[irow + 1, icol++].PutValue(resultName_3);

                //样式
                for (int i = 0; i < icol; i++)
                {
                    worksheet.Cells[irow, i].SetStyle(st_header);
                    worksheet.Cells[irow + 1, i].SetStyle(st_header);
                }

                worksheet.Cells.SetRowHeightPixel(irow, 25);
                worksheet.Cells.SetRowHeightPixel(irow + 1, 25);

                #endregion

                #region 列宽

                int[] widths = new int[] { 150, 80, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50 };
                for (int i = 0; i < widths.Length; i++)
                {
                    worksheet.Cells.SetColumnWidthPixel(i, widths[i]);
                }

                #endregion

                #region 内容
                irow = 2;
                foreach (var item in list)
                {
                    icol = 0;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(item.Deptname);
                    worksheet.Cells[irow, icol++].PutValue(item.Membername);
                    worksheet.Cells[irow, icol++].PutValue(item.Noresult);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_1_2);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_1_3);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_2_1);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_2_2);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_2_3);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_3_2);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_3_3);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_4_1);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_4_2);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_4_3);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_5_1);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_5_2);
                    worksheet.Cells[irow, icol++].PutValue(item.Result_5_3);

                    //样式
                    for (int i = 0; i < icol; i++)
                    {
                        worksheet.Cells[irow, i].SetStyle(st_border);
                    }

                    irow++;
                }
                #endregion

                //临时文件，直接存储在本地
                string path = new S_environmentService().GetValue("upload.temppath");
                //缓存Key值
                string cachekey = Guid.NewGuid().ToString().Replace("-", "");
                //缓存对象(key=临时文件名，value=下载文件名）
                var tempfile = new KeyValue
                {
                    key = cachekey + ".xls",
                    value = string.Format("{0}机关管理人员测评结果汇总.xls", DateTime.Now.ToString("yyyy"))
                };
                Utils.CreateDirectory(path);
                workbook.Save(path + tempfile.key, Aspose.Cells.SaveFormat.Excel97To2003);

                //存储文件URL
                new MemoryCacheManager().Remove(cachekey);
                new MemoryCacheManager().Set(cachekey, tempfile, 5);

                return Json(new AjaxResult(AjaxResult.RESULT_OK, "", cachekey));
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return Json(new AjaxResult(AjaxResult.RESULT_ERROR, ex.Message));
            }
        }

        #endregion

        #region 生成导出文件（好评率排名）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult CreateRankingAscExportFile(int evalId, string sort, string order)
        {
            try
            {
                var service = new T_evaluate_officememberresultService();
                var dictservice = new S_dictService();

                var reportdata = service.GetRankingResultReport(evalId);

                //生成Excel
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                int icol = 0;
                int irow = 0;

                var st_header = ExcelHelper.getHeaderStyle(workbook);
                st_header.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border = ExcelHelper.getContentStyle(workbook);
                st_border.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border_pecent = ExcelHelper.getContentStyle(workbook);
                st_border_pecent.Custom = "0.00%";
                st_border_pecent.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                #region 列宽

                int[] widths = new int[] { 150, 80, 60, 60, 150, 80, 60, 60, 150, 80, 60, 60, 150, 80, 60, 60, 150, 80, 60, 60 };
                for (int i = 0; i < widths.Length; i++)
                {
                    worksheet.Cells.SetColumnWidthPixel(i, widths[i]);
                }

                #endregion

                #region 标题1

                //标题
                worksheet.Cells[irow, 0].PutValue("服务态度");
                worksheet.Cells[irow, 4].PutValue("深入现场");
                worksheet.Cells[irow, 8].PutValue("工作效率");
                worksheet.Cells[irow, 12].PutValue("廉洁自律");
                worksheet.Cells[irow, 16].PutValue("综合评价");

                //行列合并
                worksheet.Cells.Merge(irow, 0, 1, 4);
                worksheet.Cells.Merge(irow, 4, 1, 4);
                worksheet.Cells.Merge(irow, 8, 1, 4);
                worksheet.Cells.Merge(irow, 12, 1, 4);
                worksheet.Cells.Merge(irow, 16, 1, 4);

                icol = 0;
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");

                //样式
                for (int i = 0; i < icol; i++)
                {
                    worksheet.Cells[irow, i].SetStyle(st_header);
                    worksheet.Cells[irow + 1, i].SetStyle(st_header);
                }

                worksheet.Cells.SetRowHeightPixel(irow, 25);
                worksheet.Cells.SetRowHeightPixel(irow + 1, 25);

                #endregion

                #region 内容1
                irow = 2;

                var ranking_result_1 = reportdata.OrderByDescending(p => p.Result_1_1).ToList();
                var ranking_result_2 = reportdata.OrderByDescending(p => p.Result_2_1).ToList();
                var ranking_result_3 = reportdata.OrderByDescending(p => p.Result_3_1).ToList();
                var ranking_result_4 = reportdata.OrderByDescending(p => p.Result_4_1).ToList();
                var ranking_result_5 = reportdata.OrderByDescending(p => p.Result_5_1).ToList();

                for (int i = 0; i < reportdata.Count; i++)
                {
                    icol = 0;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Result_1_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Result_2_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Result_2_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Result_3_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Result_3_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Result_3_3);

                    //样式
                    for (int c = 0; c < icol; c++)
                    {
                        if ((c + 1) % 4 == 3 || (c + 1) % 4 == 0)
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border_pecent);
                        }
                        else
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border);
                        }
                    }

                    irow++;
                }

                #endregion

                //临时文件，直接存储在本地
                string path = new S_environmentService().GetValue("upload.temppath");
                //缓存Key值
                string cachekey = Guid.NewGuid().ToString().Replace("-", "");
                //缓存对象(key=临时文件名，value=下载文件名）
                var tempfile = new KeyValue
                {
                    key = cachekey + ".xls",
                    value = string.Format("{0}机关管理人员好评率排名.xls", DateTime.Now.ToString("yyyy"))
                };
                Utils.CreateDirectory(path);
                workbook.Save(path + tempfile.key, Aspose.Cells.SaveFormat.Excel97To2003);

                //存储文件URL
                new MemoryCacheManager().Remove(cachekey);
                new MemoryCacheManager().Set(cachekey, tempfile, 5);

                return Json(new AjaxResult(AjaxResult.RESULT_OK, "", cachekey));
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return Json(new AjaxResult(AjaxResult.RESULT_ERROR, ex.Message));
            }
        }

        #endregion

        #region 生成导出文件（差评率排名）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult CreateRankingDescExportFile(int evalId, string sort, string order)
        {
            try
            {
                var service = new T_evaluate_officememberresultService();
                var dictservice = new S_dictService();

                var reportdata = new T_evaluate_officememberresultService().GetRankingResultReport(evalId);

                //生成Excel
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                int icol = 0;
                int irow = 0;

                var st_header = ExcelHelper.getHeaderStyle(workbook);
                st_header.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border = ExcelHelper.getContentStyle(workbook);
                st_border.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border_pecent = ExcelHelper.getContentStyle(workbook);
                st_border_pecent.Custom = "0.00%";
                st_border_pecent.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                #region 列宽

                int[] widths = new int[] { 150, 80, 60, 60, 150, 80, 60, 60, 150, 80, 60, 60, 150, 80, 60, 60, 150, 80, 60, 60 };
                for (int i = 0; i < widths.Length; i++)
                {
                    worksheet.Cells.SetColumnWidthPixel(i, widths[i]);
                }

                #endregion

                #region 标题1

                //标题
                worksheet.Cells[irow, 0].PutValue("服务态度");
                worksheet.Cells[irow, 4].PutValue("深入现场");
                worksheet.Cells[irow, 8].PutValue("工作效率");
                worksheet.Cells[irow, 12].PutValue("廉洁自律");
                worksheet.Cells[irow, 16].PutValue("综合评价");

                //行列合并
                worksheet.Cells.Merge(irow, 0, 1, 4);
                worksheet.Cells.Merge(irow, 4, 1, 4);
                worksheet.Cells.Merge(irow, 8, 1, 4);
                worksheet.Cells.Merge(irow, 12, 1, 4);
                worksheet.Cells.Merge(irow, 16, 1, 4);

                icol = 0;
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");

                //样式
                for (int i = 0; i < icol; i++)
                {
                    worksheet.Cells[irow, i].SetStyle(st_header);
                    worksheet.Cells[irow + 1, i].SetStyle(st_header);
                }

                worksheet.Cells.SetRowHeightPixel(irow, 25);
                worksheet.Cells.SetRowHeightPixel(irow + 1, 25);

                #endregion

                #region 内容1
                irow = 2;

                var ranking_result_1 = reportdata.OrderByDescending(p => p.Result_1_3).ToList();
                var ranking_result_2 = reportdata.OrderByDescending(p => p.Result_2_3).ToList();
                var ranking_result_3 = reportdata.OrderByDescending(p => p.Result_3_3).ToList();
                var ranking_result_4 = reportdata.OrderByDescending(p => p.Result_4_3).ToList();
                var ranking_result_5 = reportdata.OrderByDescending(p => p.Result_5_3).ToList();

                for (int i = 0; i < reportdata.Count; i++)
                {
                    icol = 0;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Result_1_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Result_2_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2[i].Result_2_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3[i].Result_3_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4[i].Result_3_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Result_3_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5[i].Result_3_3);

                    //样式
                    for (int c = 0; c < icol; c++)
                    {
                        if ((c + 1) % 4 == 3 || (c + 1) % 4 == 0)
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border_pecent);
                        }
                        else
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border);
                        }
                    }

                    irow++;
                }

                #endregion

                //临时文件，直接存储在本地
                string path = new S_environmentService().GetValue("upload.temppath");
                //缓存Key值
                string cachekey = Guid.NewGuid().ToString().Replace("-", "");
                //缓存对象(key=临时文件名，value=下载文件名）
                var tempfile = new KeyValue
                {
                    key = cachekey + ".xls",
                    value = string.Format("{0}机关管理人员差评率排名.xls", DateTime.Now.ToString("yyyy"))
                };
                Utils.CreateDirectory(path);
                workbook.Save(path + tempfile.key, Aspose.Cells.SaveFormat.Excel97To2003);

                //存储文件URL
                new MemoryCacheManager().Remove(cachekey);
                new MemoryCacheManager().Set(cachekey, tempfile, 5);

                return Json(new AjaxResult(AjaxResult.RESULT_OK, "", cachekey));
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return Json(new AjaxResult(AjaxResult.RESULT_ERROR, ex.Message));
            }
        }

        #endregion

        #region 生成导出文件（不了解/无差评）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult CreateRankingNoresultExportFile(int evalId, string sort, string order)
        {
            try
            {
                var service = new T_evaluate_officememberresultService();
                var dictservice = new S_dictService();

                //不了解率
                var reportdata = new T_evaluate_officememberresultService().GetRankingResultReport(evalId);

                //差评数统计
                var baddata = new T_evaluate_officememberresultService().GetBadResultReport(evalId);

                //无差评项
                var nobadresult = baddata
                    .Select(p => new OfficeMemberNoBadResultReportModel
                    {
                        Dept_id = p.Dept_id,
                        Deptname = p.Deptname,
                        Member_id = p.Member_id,
                        Membername = p.Membername,
                        NoBadcount = ((p.Result_1 == 0 ? 1 : 0) + (p.Result_2 == 0 ? 1 : 0) + (p.Result_3 == 0 ? 1 : 0) + (p.Result_4 == 0 ? 1 : 0) + (p.Result_5 == 0 ? 1 : 0))
                    })
                    .Where(p => p.NoBadcount > 0)
                    .OrderByDescending(p => p.NoBadcount)
                    .ToList();

                //生成Excel
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                int icol = 0;
                int irow = 0;

                var st_header = ExcelHelper.getHeaderStyle(workbook);
                st_header.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border = ExcelHelper.getContentStyle(workbook);
                st_border.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border_pecent = ExcelHelper.getContentStyle(workbook);
                st_border_pecent.Custom = "0.00%";
                st_border_pecent.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                #region 列宽

                int[] widths = new int[] { 60, 150, 80, 60, 60, 60, 60, 150, 80, 60 };
                for (int i = 0; i < widths.Length; i++)
                {
                    worksheet.Cells.SetColumnWidthPixel(i, widths[i]);
                }

                #endregion

                #region 标题1

                //标题
                worksheet.Cells[irow, 0].PutValue("不了解");
                worksheet.Cells[irow, 6].PutValue("无差评");

                //行列合并
                worksheet.Cells.Merge(irow, 0, 1, 6);
                worksheet.Cells.Merge(irow, 6, 1, 4);

                icol = 0;
                worksheet.Cells[irow + 1, icol++].PutValue("排序");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("总票数");
                worksheet.Cells[irow + 1, icol++].PutValue("不了解数");
                worksheet.Cells[irow + 1, icol++].PutValue("占比");
                worksheet.Cells[irow + 1, icol++].PutValue("排序");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("项");

                //样式
                for (int i = 0; i < icol; i++)
                {
                    worksheet.Cells[irow, i].SetStyle(st_header);
                    worksheet.Cells[irow + 1, i].SetStyle(st_header);
                }

                worksheet.Cells.SetRowHeightPixel(irow, 25);
                worksheet.Cells.SetRowHeightPixel(irow + 1, 25);

                #endregion

                #region 内容1
                irow = 2;

                var ranking_result_1 = reportdata.Where(p => p.Noresult > 0).OrderByDescending(p => p.Noresult).ToList();

                for (int i = 0; i < ranking_result_1.Count; i++)
                {
                    icol = 0;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(i + 1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Total);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Noresult);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1[i].Noresult_rate);

                    //样式
                    for (int c = 0; c < icol; c++)
                    {
                        if (c == icol - 1)
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border_pecent);
                        }
                        else
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border);
                        }
                    }

                    irow++;
                }

                irow = 2;
                for (int i = 0; i < nobadresult.Count; i++)
                {
                    icol = 6;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(i + 1);
                    worksheet.Cells[irow, icol++].PutValue(nobadresult[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(nobadresult[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(nobadresult[i].NoBadcount);

                    //样式
                    for (int c = 6; c < icol; c++)
                    {
                        worksheet.Cells[irow, c].SetStyle(st_border);
                    }

                    irow++;
                }


                #endregion

                //临时文件，直接存储在本地
                string path = new S_environmentService().GetValue("upload.temppath");
                //缓存Key值
                string cachekey = Guid.NewGuid().ToString().Replace("-", "");
                //缓存对象(key=临时文件名，value=下载文件名）
                var tempfile = new KeyValue
                {
                    key = cachekey + ".xls",
                    value = string.Format("{0}机关管理人员不了解_无差评统计.xls", DateTime.Now.ToString("yyyy"))
                };
                Utils.CreateDirectory(path);
                workbook.Save(path + tempfile.key, Aspose.Cells.SaveFormat.Excel97To2003);

                //存储文件URL
                new MemoryCacheManager().Remove(cachekey);
                new MemoryCacheManager().Set(cachekey, tempfile, 5);

                return Json(new AjaxResult(AjaxResult.RESULT_OK, "", cachekey));
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return Json(new AjaxResult(AjaxResult.RESULT_ERROR, ex.Message));
            }
        }

        #endregion

        #region 生成导出文件（结果分析）

        [AuthorizeFilter("Report.OfficeMemberResult")]
        public JsonResult CreateRankingAnalysisExportFile(int evalId, string sort, string order)
        {
            try
            {
                var service = new T_evaluate_officememberresultService();
                var dictservice = new S_dictService();

                var reportdata = service.GetRankingResultReport(evalId);

                //生成Excel
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

                int icol = 0;
                int irow = 0;

                var st_header = ExcelHelper.getHeaderStyle(workbook);
                st_header.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border = ExcelHelper.getContentStyle(workbook);
                st_border.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                var st_border_pecent = ExcelHelper.getContentStyle(workbook);
                st_border_pecent.Custom = "0.00%";
                st_border_pecent.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;

                #region 列宽

                int[] widths = new int[] { 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60, 120, 80, 60 };
                for (int i = 0; i < widths.Length; i++)
                {
                    worksheet.Cells.SetColumnWidthPixel(i, widths[i]);
                }

                #endregion

                #region 标题1

                //标题
                worksheet.Cells[irow, 0].PutValue("服务态度");
                worksheet.Cells[irow, 6].PutValue("深入现场");
                worksheet.Cells[irow, 12].PutValue("工作效率");
                worksheet.Cells[irow, 18].PutValue("廉洁自律");
                worksheet.Cells[irow, 24].PutValue("综合评价");

                //行列合并
                worksheet.Cells.Merge(irow, 0, 1, 6);
                worksheet.Cells.Merge(irow, 6, 1, 6);
                worksheet.Cells.Merge(irow, 12, 1, 6);
                worksheet.Cells.Merge(irow, 18, 1, 6);
                worksheet.Cells.Merge(irow, 24, 1, 6);

                icol = 0;
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("好评率");
                worksheet.Cells[irow + 1, icol++].PutValue("科室名称");
                worksheet.Cells[irow + 1, icol++].PutValue("人员姓名");
                worksheet.Cells[irow + 1, icol++].PutValue("差评率");

                //样式
                for (int i = 0; i < icol; i++)
                {
                    worksheet.Cells[irow, i].SetStyle(st_header);
                    worksheet.Cells[irow + 1, i].SetStyle(st_header);
                }

                worksheet.Cells.SetRowHeightPixel(irow, 25);
                worksheet.Cells.SetRowHeightPixel(irow + 1, 25);

                #endregion

                #region 内容1
                irow = 2;

                var ranking_result_1_1 = reportdata.OrderByDescending(p => p.Result_1_1).ToList();
                var ranking_result_1_3 = reportdata.OrderByDescending(p => p.Result_1_3).ToList();
                var ranking_result_2_1 = reportdata.OrderByDescending(p => p.Result_2_1).ToList();
                var ranking_result_2_3 = reportdata.OrderByDescending(p => p.Result_2_3).ToList();
                var ranking_result_3_1 = reportdata.OrderByDescending(p => p.Result_3_1).ToList();
                var ranking_result_3_3 = reportdata.OrderByDescending(p => p.Result_3_3).ToList();
                var ranking_result_4_1 = reportdata.OrderByDescending(p => p.Result_4_1).ToList();
                var ranking_result_4_3 = reportdata.OrderByDescending(p => p.Result_4_3).ToList();
                var ranking_result_5_1 = reportdata.OrderByDescending(p => p.Result_5_1).ToList();
                var ranking_result_5_3 = reportdata.OrderByDescending(p => p.Result_5_3).ToList();

                for (int i = 0; i < reportdata.Count; i++)
                {
                    icol = 0;

                    //内容
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_1_3[i].Result_1_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_2_3[i].Result_1_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_3_3[i].Result_1_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_4_3[i].Result_1_3);

                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5_1[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5_1[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5_1[i].Result_1_1);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5_3[i].Deptname);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5_3[i].Membername);
                    worksheet.Cells[irow, icol++].PutValue(ranking_result_5_3[i].Result_1_3);

                    //样式
                    for (int c = 0; c < icol; c++)
                    {
                        if ((c + 1) % 6 == 3 || (c + 1) % 6 == 0)
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border_pecent);
                        }
                        else
                        {
                            worksheet.Cells[irow, c].SetStyle(st_border);
                        }
                    }

                    irow++;
                }

                #endregion

                //临时文件，直接存储在本地
                string path = new S_environmentService().GetValue("upload.temppath");
                //缓存Key值
                string cachekey = Guid.NewGuid().ToString().Replace("-", "");
                //缓存对象(key=临时文件名，value=下载文件名）
                var tempfile = new KeyValue
                {
                    key = cachekey + ".xls",
                    value = string.Format("{0}机关管理人员测评结果分析.xls", DateTime.Now.ToString("yyyy"))
                };
                Utils.CreateDirectory(path);
                workbook.Save(path + tempfile.key, Aspose.Cells.SaveFormat.Excel97To2003);

                //存储文件URL
                new MemoryCacheManager().Remove(cachekey);
                new MemoryCacheManager().Set(cachekey, tempfile, 5);

                return Json(new AjaxResult(AjaxResult.RESULT_OK, "", cachekey));
            }
            catch (Exception ex)
            {
                log.Error(ex);
                return Json(new AjaxResult(AjaxResult.RESULT_ERROR, ex.Message));
            }
        }

        #endregion

        #region 下载Excel文件

        [IgnoreAuthorizeFilter]
        public ActionResult DownFile()
        {
            var cachekey = Request.QueryString["key"];

            var tempfile = new MemoryCacheManager().Get<KeyValue>(cachekey);

            //string url = new MemoryCacheManager().Get<string>(cachekey);
            if (tempfile != null)
            {
                string path = new S_environmentService().GetValue("upload.temppath");
                string tempfilename = path + tempfile.key;

                return File(tempfilename, "application/x-excel", tempfile.value);
            }
            else
            {
                throw new HttpException(404, "文件不存在！");
            }
        }

        #endregion

    }
}